import sys
import io
from urllib import request
from bs4 import BeautifulSoup
import MySQLdb

#测试用git

# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "lxyessc_2020", "essc_m_wx", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf8') #改变标准输出的默认编码

MAXID = 4507999
for x in range(4477300, 4477305):
    #登录后才能访问的网站
    url = 'https://m.tnc.com.cn/company/d'+ str(x) + '/introduce/?introduceFlag=introduce'

    #浏览器登录后得到的cookie，也就是刚才复制的字符串
    cookie_str = r'SECURITY_TNC_MEMBER=YmFhZGExNmYtYzliYi00N2EzLWE3NTYtZTdmN2ZmZWUzZTg1; Hm_lvt_e82a92eb6606539ba3fcb86e4d841f73=1637636730,1637665427; _ga=GA1.3.993341141.1637665427; _gid=GA1.3.147369249.1637665427; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2217d4c774ece82-0f652b0e276be-978183a-2073600-17d4c774ecf1db%22%2C%22first_id%22%3A%22%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%2217d4c774ece82-0f652b0e276be-978183a-2073600-17d4c774ecf1db%22%7D; _de_us=8ceed37ab36b6517f612c344a9f4fd50f6e00a58; m_c_=699607; JSESSIONID=abczn1qfKhd4H7yv2Cm1x; Hm_lvt_3dda01b598c3ad1285e97106fad0d683=1637648950,1637665469; Hm_lpvt_e82a92eb6606539ba3fcb86e4d841f73=1637665753; _err_times=0; __qfcday=1547433352; _gat=1; __qfcc=axapjd=%7C%7C4477288%7C99%7C%7Ctnc_mobile.cskde4=||4507078|99||tnc_mobile; _gali=loginForm; _de_fs=a8637ebc2819433cb2f0e73cd59a53db; __qfca=1546145289.1637665469.1637731603.1637731608.47; Hm_lpvt_3dda01b598c3ad1285e97106fad0d683=1637731608'

    # #登录后才能访问的网页
    # url = 'http://ssfw.xmu.edu.cn/cmstar/index.portal'

    req = request.Request(url)
    #设置cookie
    req.add_header('cookie', cookie_str)
    #设置请求头
    req.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36')

    resp = request.urlopen(req)

    soup = BeautifulSoup(resp.read().decode('utf-8'))

    # print(soup)
    row = {'id':x,'name':'','address':'','tel1':'','tel2':'','other':''}
    for tag in soup.find_all('p'):
        if tag.get('class') is None:
            continue
        if 'comp-name' in tag.get('class'):
            row['name']=tag.text
            # print("公司名称：", tag.text)

    index = 0
    for tag in soup.find_all('div'):
        if tag.get('class') is None:
            continue
        if 'summary-content' in tag.get('class'):
            if index == 0:
                #print("公司名称：", tag.text)
                index = index + 1
                continue
            elif index == 1:
                row['address'] = tag.text
                # print("地址：", tag.text)
            elif index == 2:
                row['tel1'] = tag.text
                # print("手机：", tag.text)
            elif index == 3:
                row['tel2'] = tag.text
                # print("电话：", tag.text)
            elif index == 4:
                row['other'] = tag.text
                # print("其他：", tag.text)
            index = index + 1
    print(row)
    partition = ''
    if '苏州' in row['address'] or '苏州' in row['name']:
        partition = '苏州'
    elif '绍兴' in row['address'] or '绍兴' in row['name']:
        partition = '绍兴'
    elif '无锡' in row['address'] or '无锡' in row['name']:
        partition = '无锡'
    elif '常州' in row['address'] or '常州' in row['name']:
        partition = '常州'
    elif '宜兴' in row['address'] or '宜兴' in row['name']:
        partition = '宜兴'
    elif '江阴' in row['address'] or '江阴' in row['name']:
        partition = '江阴'
    elif '徐州' in row['address'] or '徐州' in row['name']:
        partition = '徐州'
    # SQL 插入语句
    sql = "INSERT INTO CRAWLDATA(ID, \
           NAME,ADDRESS, TEL1, TEL2, MEMO, PART) \
           VALUES (%s, '%s', '%s', '%s', '%s', '%s' , '%s' )" % \
          (row['id'],row['name'], row['address'], row['tel1'], row['tel2'], row['other'],partition)
    try:
        # 执行sql语句
        # print(sql)
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 发生错误时回滚
        db.rollback()

# 关闭数据库连接
db.close()